﻿using System;
using System.Data;
using System.Web.Services;

namespace WMSWebService
{
    /// <summary>
    /// SQLDBService 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class SQLDBService : System.Web.Services.WebService
    {

        [WebMethod]
        public int ExcuteScalarSQL(string sql)
        {
            var result = SqlDBHelper.ExcuteScalarSQL(sql);
            return result;
        }
        [WebMethod]
        public DataTable GetTable(string sql)
        {
            var result = SqlDBHelper.GetTable(sql);

            result.TableName = "Result";
            return result;
        }
        [WebMethod]
        public int ExcuteSQL(string sql)
        {
            var result = SqlDBHelper.ExcuteSQL(sql);
            return result;
        }
        [WebMethod]
        public DataSet GetDataSet(string sql)
        {
            var result = SqlDBHelper.GetDataSet(sql);

            return result;
        }
        //领料出库
        [WebMethod]
        public bool RMOutStock(string billNo, string boxNo, decimal qty, string locationSN,
            string partNumber, string dateCode, string lotNo, string CustomerSN, DateTime ExpiryDate,
            string UniqueCode, string Ext1, string rowno, string userName)
        {
            var result = false;

            try
            {

                var inventorySql = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}' and  PartNumber='{1}'", locationSN, partNumber);

                var inventoryInfo = SqlDBHelper.GetTable(inventorySql);

                if (inventoryInfo.Rows.Count == 0)
                {
                    return false;
                }

                var inv_Qty = Convert.ToDecimal(inventoryInfo.Rows[0]["Qty"]);

                if (inv_Qty < qty)
                {
                    return false;
                }
                var inventorySqlAll = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}'", locationSN);

                var inventoryInfoAll = SqlDBHelper.GetTable(inventorySqlAll);

                int invencount = inventoryInfoAll.Rows.Count;

                string productSearch_Sql = string.Format(@"SELECT sr.* FROM dbo.wms_T_OutStockRMRows sr
LEFT JOIN dbo.wms_T_OutStockRM os ON sr.BillNo=os.BillNo
WHERE  sr.BillNo='{0}' AND sr.PartNumber='{1}' and sr.RowNo='{2}'", billNo, partNumber, rowno);

                DataTable dt = SqlDBHelper.GetTable(productSearch_Sql);

                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                var row = dt.Rows[0];

                var storageSN = inventoryInfo.Rows[0]["Storage_SN"].ToString();
                var areaSN = inventoryInfo.Rows[0]["Area_SN"].ToString();
                var rowNo = Convert.ToInt32(row["RowNo"]);
                var orderNo = Convert.ToInt32(row["OrderNo"]);
                var relatedBill = row["RelatedBill"].ToString();
                //var version = inventoryInfo.Rows[0]["Version"].ToString();
                var version = row["Ext3"].ToString();
                var unit = inventoryInfo.Rows[0]["Unit"].ToString();

                var guid = inventoryInfo.Rows[0]["Guid"].ToString();

                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutStockRMRowDetails]
           ([BillNo]
           ,[RelatedBill]
           ,[PartNumber]
           ,[RowNo]
           ,[OrderNo]
           ,[BoxNo]
           ,[Version]
           ,[DateCode]
           ,[SupLotNo]
           ,[PlanQty]
           ,[RealQty]
           ,[Unit]
           ,[Status]
           ,[Creator]
           ,[CreateTime]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[CustomerSN]
           ,[ExpiryDate]
           ,[UniqueCode],Ext1)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,{3}
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,getdate()
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}','{20}');", billNo, relatedBill, partNumber, rowNo, orderNo, ' ', version, dateCode, lotNo, qty, qty, unit, "0",
               userName, storageSN, areaSN, locationSN, CustomerSN, ExpiryDate, UniqueCode, Ext1);

                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRows]
   SET  [RealQty] = (SELECT SUM([RealQty]) FROM [wms_T_OutStockRMRowDetails] WHERE BillNo='{0}' AND RowNo='{1}'  and status<>'7')
 WHERE  BillNo='{0}' AND RowNo='{1}';
UPDATE dbo.wms_T_OutStockRM SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{0}'  ;", billNo, rowNo, userName);

                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (inv_Qty == qty)
                {
                    sql3 = string.Format(@"delete  FROM [dbo].[wms_T_Inventory] WHERE    GUID='{0}';", guid);
                    if (invencount < 2)
                    {
                        sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);
                    }
                }
                else if (inv_Qty > qty)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty-{0} WHERE GUID='{1}';", qty, guid);
                }

                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        "-" + qty, userName, "O", UniqueCode, lotNo, relatedBill, billNo);

                var sqlCmd = (sql + sql2 + sql3 + sql4 + sql6).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = SqlDBHelper.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }

        //成品出库
        [WebMethod]
        public bool ProductOutStock(string billNo, string boxNo, int qty,
                                    string locationSN, string partNumber, string userName)
        {
            var result = false;

            try
            {
                var inventorySql = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}' and  PartNumber='{1}'", locationSN, partNumber);
                var inventoryInfo = SqlDBHelper.GetTable(inventorySql);
                if (inventoryInfo.Rows.Count == 0)
                {
                    return false;
                }

                var inv_Qty = Convert.ToInt32(inventoryInfo.Rows[0]["Qty"]);
                if (inv_Qty < qty)
                {
                    return false;
                }

                string productSearch_Sql = string.Format(@"SELECT sr.*,os.RelatedBill,os.Ext1 as TargetStorage FROM dbo.wms_T_OutStockRows sr
LEFT JOIN dbo.wms_T_OutStock os ON sr.BillNo=os.BillNo
WHERE  sr.BillNo='{0}' AND sr.ProductNumber='{1}'", billNo, partNumber);

                DataTable dt = SqlDBHelper.GetTable(productSearch_Sql);

                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                var row = dt.Rows[0];

                var storageSN = inventoryInfo.Rows[0]["Storage_SN"].ToString();
                var areaSN = inventoryInfo.Rows[0]["Area_SN"].ToString();
                var rowNo = row["RowNo"].ToString();
                var relatedBill = row["RelatedBill"].ToString();
                var targetStorage = row["TargetStorage"].ToString();
                var version = inventoryInfo.Rows[0]["Version"].ToString();
                var dateCode = inventoryInfo.Rows[0]["DateCode"].ToString();
                var lotNo = inventoryInfo.Rows[0]["LotNo"].ToString();
                var unit = inventoryInfo.Rows[0]["Unit"].ToString();


                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutStockRowDetails]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[BillNo]
           ,[RowNo]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[LotNo]
           ,[BoxNo]
           ,[Creator]
           ,[CreateTime]
           ,[Qty]
           ,[Status]
           ,[Unit])
		   VALUES
           (NEWID()
      ,'{0}'
      ,'{1}'
      ,'{2}'
	  ,'{3}'
	  ,'{4}'
      ,'{5}'
      ,'{6}'
      ,'{7}'
	  ,'{8}'
	  ,'{9}'
	  ,'{10}'
      ,GETDATE()
      ,'{11}'
      ,'1'
      ,'{12}');", storageSN, areaSN, locationSN, billNo, rowNo, partNumber, version, dateCode, lotNo, boxNo,
               userName, qty, unit);

                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRows]
   SET  [SelectQty] = (SELECT SUM([Qty]) FROM [wms_T_OutStockRowDetails] WHERE BillNo='{0}' AND RowNo='{1}' AND status='1')
 WHERE  BillNo='{0}' AND RowNo='{1}';
UPDATE dbo.wms_T_OutStock SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{0}';", billNo, rowNo, userName);

                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (inv_Qty == qty)
                {
                    sql3 = string.Format(@"delete  FROM [dbo].[wms_T_Inventory] WHERE  Location_SN='{0}' and PartNumber='{1}';", locationSN, partNumber);
                    sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);
                }
                else if (inv_Qty > qty)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty-{0} WHERE Location_SN='{1}' and PartNumber='{2}';", qty, locationSN, partNumber);

                }
                var sql5 = string.Format(@"UPDATE  dbo.wms_T_InStockRowDetails SET  RealQty=RealQty-{0} WHERE BoxNo='{1}' and status='0';", qty, boxNo);
                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        "-" + qty, userName, "O", boxNo, lotNo, relatedBill, billNo);

                var sqlCmd = (sql + sql2 + sql3 + sql4 + sql5 + sql6).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
                + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = SqlDBHelper.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }

        //成品获取推荐库位
        [WebMethod]
        public DataTable GetRecommendLocationSN(string regionName, string storageSN, string billNo)
        {
            string sql = string.Format(@"SELECT os.RelatedBill,sr.* FROM dbo.wms_T_outStock os
LEFT JOIN dbo.wms_T_OutStockRows sr ON os.BillNo=sr.BillNo
WHERE os.BillNo='{0}' and  ISNULL( sr.PlanQty,0)> ISNULL(sr.SelectQty,0)", billNo);
            var stockRowsDT = SqlDBHelper.GetTable(sql);
            DataTable dt = new DataTable();
            dt.TableName = "tmp";
            foreach (DataRow row in stockRowsDT.Rows)
            {
                var relatedBill = row["RelatedBill"].ToString();
                var productNumber = row["ProductNumber"].ToString();
                var planQty = 0;
                if (!string.IsNullOrEmpty(row["PlanQty"].ToString()))
                {
                    planQty = Convert.ToInt32(row["PlanQty"]);
                }

                var rowNo = row["RowNo"].ToString();

                //                var deleteSql = string.Format(@"DELETE FROM [dbo].[wms_T_OutStockRowDetails] 
                //WHERE Status='0' AND BillNo='{0}' AND RowNo='{1}'", billNo, rowNo);
                //                Utils.NMS.ExcuteSQL(deleteSql);

                var lotSql = string.Format(@"SELECT * FROM wms_R_LotSelect WHERE RelatedBill='{0}' AND PartNumber='{1}'", relatedBill, productNumber);

                var lotSelect = SqlDBHelper.GetTable(lotSql);

                var rowSql = string.Empty;
                if (lotSelect.Rows.Count == 0)
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y'  ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y' and it.Storage_SN='{2}'  ORDER BY it.DateCode, it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN);
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(storageSN))
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y'
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{2}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, relatedBill);
                    }
                    else
                    {
                        rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and it.Storage_SN='{2}' and sa.Assigned_Type='Y'
and  it.LotNo IN (SELECT DISTINCT LotNo FROM wms_R_LotSelect WHERE RelatedBill='{3}' AND PartNumber='{1}')
ORDER BY it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN, relatedBill);
                    }

                }

                DataTable rowDT = SqlDBHelper.GetTable(rowSql);

                DataTable addRowDt = rowDT.Copy();

                if (rowDT.Rows.Count > 0)
                {
                    int realqty = 0;
                    if (!string.IsNullOrEmpty(row["SelectQty"].ToString()))
                    {
                        realqty = Convert.ToInt32(row["SelectQty"]);
                    }

                    foreach (DataRow rDR in rowDT.Rows)
                    {
                        var qty = 0;

                        if (!string.IsNullOrEmpty(rDR["Qty"].ToString()))
                        {
                            qty = Convert.ToInt32(rDR["Qty"]);
                        }

                        addRowDt.Rows.Add(rDR.ItemArray);


                        if (planQty <= (realqty + qty))
                        {
                            break;
                        }
                        realqty += qty;
                    }

                }
                dt.Merge(addRowDt);
            }
            return dt;
        }
        //移库下架
        [WebMethod]
        public bool TransferOut(string billNo, string locationSN, decimal qty, string userName)
        {
            var result = false;
            try
            {
                string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);
                var inventory = SqlDBHelper.GetTable(sql);

                var sql2 = string.Empty;
                DataRow row;

                if (inventory.Rows.Count > 1)
                {
                    return false;
                }

                if (inventory.Rows.Count > 0)
                {
                    row = inventory.Rows[0];
                    var inv_Qty = Convert.ToDecimal(inventory.Rows[0]["Qty"]);

                    if (inv_Qty < qty)
                    {
                        return result;
                    }
                    else if (inv_Qty == qty)
                    {
                        sql2 = string.Format(@"DELETE FROM dbo.wms_T_Inventory WHERE Guid='{0}';
UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='0'  WHERE Location_SN ='{1}';", inventory.Rows[0]["Guid"].ToString(), locationSN);
                    }
                    else
                    {
                        sql2 = string.Format("UPDATE dbo.wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", inventory.Rows[0]["Guid"].ToString(), qty);
                    }
                }
                else
                {
                    return false;
                }

                string sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), row["Storage_SN"].ToString(), row["Area_SN"].ToString(), row["Location_SN"].ToString(), row["PartNumber"].ToString(),
                            -qty, userName, "O", "", billNo, row["LotNo"].ToString());

                string sql4 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status],Version,DateCode,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{10}'
           ,'{6}','{7}','{8}','{9}');", Guid.NewGuid(), row["Storage_SN"].ToString(), "", billNo, row["PartNumber"].ToString(), qty, inventory.Rows[0]["Status"], row["Version"].ToString(), row["DateCode"].ToString(), row["LotNo"].ToString(), DateTime.Parse(row["EnterTime"].ToString()));

                string sql5 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), "", "", billNo, row["PartNumber"].ToString(),
                        qty, userName, "I", "", billNo, row["LotNo"].ToString());
                string sql6 = string.Format(@"UPDATE [dbo].[wms_T_Transfer]
   SET 
      [Status] = '1'
      ,[Updator] = '{1}'
      ,[UpdateTime] = GETDATE()
 WHERE  BillNo='{0}';", billNo, userName);


                string sqlCmd = (sql2 + sql3 + sql4 + sql5 + sql6).TrimEnd(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = SqlDBHelper.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {
            }

            return result;
        }

        //移库下架
        [WebMethod]
        public bool RMTransferOut(string billNo, string locationSN, decimal qty, string PartNumber, string userName)
        {
            var result = false;
            try
            {

                string sqlCout = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);
                var inventoryCount = SqlDBHelper.GetTable(sqlCout);

                string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and PartNumber='{1}'", locationSN, PartNumber);
                var inventory = SqlDBHelper.GetTable(sql);

                var sql2 = string.Empty;
                DataRow row;

                if (inventory.Rows.Count > 0)
                {
                    row = inventory.Rows[0];
                    var inv_Qty = Convert.ToDecimal(inventory.Rows[0]["Qty"]);

                    if (inv_Qty < qty)
                    {
                        return result;
                    }
                    else if (inv_Qty == qty)
                    {
                        sql2 = string.Format(@"DELETE FROM dbo.wms_T_Inventory WHERE Guid='{0}';", inventory.Rows[0]["Guid"].ToString());
                        if (inventoryCount.Rows.Count < 2)
                        {
                            sql2 = sql2 + string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='0'  WHERE Location_SN ='{1}';", inventory.Rows[0]["Guid"].ToString(), locationSN);
                        }
                    }
                    else
                    {
                        sql2 = string.Format("UPDATE dbo.wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", inventory.Rows[0]["Guid"].ToString(), qty);
                    }
                }
                else
                {
                    return false;
                }

                string sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), row["Storage_SN"].ToString(), row["Area_SN"].ToString(), row["Location_SN"].ToString(), row["PartNumber"].ToString(),
                            -qty, userName, "O", "", billNo, row["LotNo"].ToString());

                string sql4 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status],Version,DateCode,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{10}'
           ,'{6}','{7}','{8}','{9}');", Guid.NewGuid(), row["Storage_SN"].ToString(), "", billNo, row["PartNumber"].ToString(), qty, inventory.Rows[0]["Status"], row["Version"].ToString(), row["DateCode"].ToString(), row["LotNo"].ToString(), DateTime.Parse(row["EnterTime"].ToString()));

                string sql5 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), "", "", billNo, row["PartNumber"].ToString(),
                        qty, userName, "I", "", billNo, row["LotNo"].ToString());
                string sql6 = string.Format(@"UPDATE [dbo].[wms_T_Transfer]
   SET 
      [Status] = '1'
      ,[Updator] = '{1}'
      ,[UpdateTime] = GETDATE()
 WHERE  BillNo='{0}';", billNo, userName);


                string sqlCmd = (sql2 + sql3 + sql4 + sql5 + sql6).TrimEnd(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
                + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = SqlDBHelper.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {
            }

            return result;
        }

        //退拣
        [WebMethod]
        public bool RMPR(string billNo, string RelatedBill, string partNumber, int rowNo, int orderNo, string version,
                                string dateCode, string lotNo, decimal PlanQty, decimal RealQty, string Unit, string storageSN,
                                string areaSN, string locationSN, string CustomerSN, DateTime ExpiryDate,
                                string UniqueCode, string Ext1, string oldLocation, bool boolToERP, bool InveUsed, string userName)
        {
            var result = false;
            var status = "2";
            if (boolToERP)
            {
                status = "0";
            }

            status = "(select case when count(0)>0 then '0' else '2' end from wms_T_OutStockRMRowDetails where status = 9 and BillNo='{0}' and  UniqueCode='{1}')";
            status = string.Format(status, billNo, UniqueCode);
            try
            {

                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutReturn]
           ([BillNo]
           ,[RelatedBill]
           ,[PartNumber]
           ,[RowNo]
           ,[OrderNo]
           ,[BoxNo]
           ,[Version]
           ,[DateCode]
           ,[SupLotNo]
           ,[PlanQty]
           ,[RealQty]
           ,[Unit]
           ,[Status]
           ,[Creator]
           ,[CreateTime]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[CustomerSN]
           ,[ExpiryDate]
           ,[UniqueCode],Ext1,Ext3)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,{3}
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,{9}
           ,{10}
           ,'{11}'
           ,{12}
           ,'{13}'
           ,getdate()
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}','{20}','{21}');", billNo, RelatedBill, partNumber, rowNo, orderNo, ' ', version, dateCode, lotNo, PlanQty, RealQty, Unit, status,
               userName, storageSN, areaSN, locationSN, CustomerSN, ExpiryDate, UniqueCode, Ext1, oldLocation);


                var inventorySql = string.Format(@"DELETE wms_T_OutStockRMRowDetails WHERE BillNo='{1}' and  UniqueCode='{0}'", UniqueCode, billNo);

                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRows]
                   SET  [RealQty] = (SELECT SUM([RealQty]) FROM [wms_T_OutStockRMRowDetails] WHERE BillNo='{0}' AND RowNo='{1}' and status<>'7')
                 WHERE  BillNo='{0}' AND RowNo='{1}';", billNo, rowNo, userName, orderNo);

                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (!InveUsed)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty+{0} WHERE Location_SN='{1}' and PartNumber='{2}';", RealQty, locationSN, partNumber);


                }
                else
                {

                    sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[Qty]
           ,[EnterTime]
           ,[Status]
           ,[LotNo]
           ,[Unit]
          )
values( NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',{6},GETDATE(),'1','{7}','{8}');", storageSN, areaSN, locationSN, partNumber, version, dateCode, RealQty, lotNo, Unit);
                    sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);
                }


                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2,Ext3)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}','{12}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        RealQty, userName, "I", UniqueCode, lotNo, RelatedBill, billNo, "退捡");

                //Utils.NMS.ExcuteSQL(insertHis_Sql);

                var sqlCmd = (sql + inventorySql + sql2 + sql3 + sql4 + sql6).Trim(';');
                //var sqlCmd = (sql + sql3 + sql4 + sql6).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = SqlDBHelper.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }

    }
}
